CREATE TABLE jms_dws.dws_breakoff_agg_taking (
                                                 dt                     DATE COMMENT '日期',
                                                 network_code           VARCHAR(50) COMMENT '责任机构编码',
                                                 network_name           VARCHAR(300) COMMENT '责任机构名称',
                                                 order_source_code            VARCHAR(50) COMMENT '订单来源编码(抖音-紫金山: 1085, 快手-逍遥峰: 1133,拼多多-桃花岛: D60,D09)',
                                                 order_source_name        VARCHAR(300) COMMENT '订单细分来源名称(抖音-紫金山: 1085, 快手-逍遥峰: 1133,拼多多-桃花岛: D60,D09)',
                                                 agent_name VARCHAR(300) COMMENT '代理区名称',
                                                 agent_code VARCHAR(50) COMMENT '代理区编码',
                                                 franchisee_name        VARCHAR(300) COMMENT '加盟商名称',
                                                 franchisee_code        VARCHAR(50) COMMENT '加盟商编码',
                                                 breakoff_cnt           BIGINT COMMENT '发生中断次数',
                                                 breakoff_24h_cnt       BIGINT COMMENT '中断超过 24 小时次数',
                                                 breakoff_48h_cnt       BIGINT COMMENT '中断超过 48 小时次数',
                                                 breakoff_72h_cnt       BIGINT COMMENT '中断超过 72 小时次数',
                                                 breakoff_96h_cnt       BIGINT COMMENT '中断超过 96 小时次数',
                                                 breakoff_120h_cnt      BIGINT COMMENT '中断超过 120 小时次数'
) ENGINE=OLAP
    DUPLICATE KEY(`dt`, `network_code`)
    COMMENT '平台中断汇总表(揽收时间)'
    PARTITION BY RANGE(`dt`)
    (
    PARTITION  p20211116 VALUES [("2021-11-16"), ("2021-11-17")),
    PARTITION  p20211117 VALUES [("2021-11-17"), ("2021-11-18")),
    PARTITION  p20211118 VALUES [("2021-11-18"), ("2021-11-19")),
    PARTITION  p20211119 VALUES [("2021-11-19"), ("2021-11-20")),
    PARTITION  p20211120 VALUES [("2021-11-20"), ("2021-11-21")),
    PARTITION  p20211121 VALUES [("2021-11-21"), ("2021-11-22")),
    PARTITION  p20211122 VALUES [("2021-11-22"), ("2021-11-23")),
    PARTITION  p20211123 VALUES [("2021-11-23"), ("2021-11-24")),
    PARTITION  p20211124 VALUES [("2021-11-24"), ("2021-11-25")),
    PARTITION  p20211125 VALUES [("2021-11-25"), ("2021-11-26")),
    PARTITION  p20211126 VALUES [("2021-11-26"), ("2021-11-27")),
    PARTITION  p20211127 VALUES [("2021-11-27"), ("2021-11-28")),
    PARTITION  p20211128 VALUES [("2021-11-28"), ("2021-11-29")),
    PARTITION  p20211129 VALUES [("2021-11-29"), ("2021-11-30")),
    PARTITION  p20211130 VALUES [("2021-11-30"), ("2021-12-01")),
    PARTITION  p20211201 VALUES [("2021-12-01"), ("2021-12-02")),
    PARTITION  p20211202 VALUES [("2021-12-02"), ("2021-12-03")),
    PARTITION  p20211203 VALUES [("2021-12-03"), ("2021-12-04")),
    PARTITION  p20211204 VALUES [("2021-12-04"), ("2021-12-05")),
    PARTITION  p20211205 VALUES [("2021-12-05"), ("2021-12-06")),
    PARTITION  p20211206 VALUES [("2021-12-06"), ("2021-12-07")),
    PARTITION  p20211207 VALUES [("2021-12-07"), ("2021-12-08"))
    )
    DISTRIBUTED BY HASH(`network_code`) BUCKETS 10
    PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-180",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "10",
    "in_memory" = "false",
    "storage_format" = "V2"
    );


ALTER TABLE jms_dws.dws_breakoff_agg_taking
    ADD COLUMN network_type VARCHAR(50) KEY comment '机构类型：1.中心，2.集散点，3.网点' AFTER network_code;

ALTER TABLE jms_dws.dws_breakoff_agg_taking
    ADD COLUMN next_scan_type VARCHAR(50) KEY  comment '下一机构类型：1.中心，2.集散点，3.网点' AFTER network_type;

ALTER TABLE jms_dws.dws_breakoff_agg_taking
    ADD COLUMN mage_region_code VARCHAR(50) KEY comment '管理大区编码' AFTER next_scan_type;

ALTER TABLE jms_dws.dws_breakoff_agg_taking
    ADD COLUMN mage_region_name VARCHAR(300)  comment '管理大区名称' AFTER mage_region_code;